<?php

function win_observation_bulk_upload() {
  $html = '<div style="width:100%; text-align: center;"> We are working on this feature. This will allow to upload multiple biodiversity observation records collated in MS Excel, CSV files etc.  <img alt="Work in Progress" src="'.base_path().'sites/default/files/work_in_progress.png" /></div>';
  return $html;
  //return win_observation_bulk_upload_ui();
}

function win_observation_bulk_upload_ui() {
  $html = '';
  $base_path = base_path();

  $path = drupal_get_path('module','win_observation');
  drupal_add_js($path . '/js/external/jquery-1.3.2.js');
  drupal_add_js($path . '/js/external/jquery.form.js');
  drupal_add_js($path . '/js/external/jquery.selectboxes.js');
  drupal_add_js($path . '/js/external/ui.core.js');
  drupal_add_js($path . '/js/external/ui.draggable.js');
  drupal_add_js($path . '/js/external/ui.resizable.js');
  drupal_add_js($path . '/js/external/ui.dialog.js');
  drupal_add_js($path . '/js/functions.js');
  drupal_add_js($path . '/js/win_observation_bulk_upload.js');

  drupal_add_css($path . '/css/jquery-ui.css');
  drupal_add_css($path . '/css/win_observation_bulk_upload.css');

  $cols_arr = get_table_columns('win_observations', TRUE);
  $db_cols = array();
  $skip_cols = array('id', 's_taxa', 's_scientific_name', 'col2009ac_species_id', 'is_date_range', 'from_date', 'to_date', 'is_exact_time', 'at_time', 'is_waterbody_related', 'observer_id', 'created_by', 'created_date', 'modified_by', 'modified_date', 'topology');
  foreach ($cols_arr as $col) {
    if (!in_array($col['name'], $skip_cols)) {
      $db_cols['win_observations.' . $col['name']] = (empty($col['description']) ? $col['name'] : $col['description']);
    }
  }
  $cols_arr = get_table_columns('win_observation_location', TRUE);
  $skip_cols = array('id', 'observation_id');
  foreach ($cols_arr as $col) {
    if (!in_array($col['name'], $skip_cols)) {
      $db_cols['win_observation_location.' . $col['name']] = (empty($col['description']) ? $col['name'] : $col['description']);
    }
  }
  $cols_arr = get_table_columns('win_observer', TRUE);
  $skip_cols = array('id');
  foreach ($cols_arr as $col) {
    if (!in_array($col['name'], $skip_cols)) {
      $db_cols['win_observer.' . $col['name']] = (empty($col['description']) ? $col['name'] : $col['description']);
    }
  }


  $js = '<script language="javascript">';
  $js .= 'var db_cols = [];';
  $js .= 'db_cols["latitude"] = "Latitude";';
  $js .= 'db_cols["longitude"] = "Longitude";';
  foreach ($db_cols as $col_name => $col_desc) {
    $js .= 'db_cols["' . $col_name . '"] = "'.$col_desc.'";';
  }
  $js .= '</script>';
  
  $template_url = base_path() . $path . '/files/template_spandan_bulk_upload_point.csv';

  $html .= <<<EOT
  <div class="messages warning">
    Currently the system has partial support for bulk upload of data. Following are the constraints:
    <ul>
      <li>Only CSV format is supported
      <li>Only geographical point data support
      <li>Data where geographical location is missing will be ignored
      <li>Lat Lon values only in the form of decimals(not in meters) are accepted currently. Eg. 12.3456789
      <li>Exact date of observation should be known
      <li>Observation time should be specified in hours and minutes
      <li>Images need to be uploaded separately for each observation
      <li>You can download the template csv <a href="{$template_url}">here</a>, add your data to the file for easy upload.
    </ul>
  </div>

  <center><input id="btnBulkUpload" type="button" value="Upload Data" /></center>

  <div id="divBulkUpload" title="Upload Data">
    <form action="{$base_path}observation/data" id="bulkuploadform" enctype="multipart/form-data" method="post">
      <!--<input id="progress_key" name="UPLOAD_IDENTIFIER" type="hidden" value="&lt;?= $uuid ?&gt;" />-->
      <div>
        <input name="bulkuploadfile" type="file" size="30"/>
      </div>
      <div style="padding: 5px;">
        <label for="delimiter">Delimiter</label>
        <select id="delimiter">
          <option value=','>comma</option>
          <option value=';'>semicolon</option>
          <option value='\t'>tab</option>
        </select>
        <label for="enclosure">Enclosure</label>
        <select id="enclosure">
          <option value='"'>NONE</option>
          <option value='"'>"</option>
        </select>
      </div>
      <div>
        <input type="submit" value="Upload" />
      </div>
      <!--<span id="uploadprogressbar" class="progressbar">0%</span>-->
      <input type="hidden" name="request" id="upload_request" /> <!-- This is tweak to sent the parameters in JSON -->
    </form>
  </div>

  <div id="divBulkDataMapping" title="Data Mapping">
    <input type="hidden" id="uploaded_filename"/>
    <fieldset style="margin: 0px 0px 7px; padding: 0px;">
      <div style="width:600px">
        <div style="width: 235px; float:left;">
          <div>DB Columns</div>
          <select id="dbCols" style="width:200px">
          </select>
        </div>
        <div style="width: 70px; float:left; padding-top: 45px;">
          <input type="button" value="Map" id="btnMap" />
        </div>
        <div style="width: 235px; float:left;">
          <div>Data Columns</div>
          <select id="dataCols" style="width:200px">
          </select>
        </div>
      </div>
    </fieldset>
    <fieldset style="margin: 0px 0px 7px; padding: 0px;">
      <div style="width:600px">
        <div style="width: 235px; float:left;">
          <div>DB Mapped Columns</div>
          <select id="dbMappedCols" size="10" style="width:200px">
          </select>
        </div>
        <div style="width: 70px; float:left; padding-top: 120px;">
          <input type="button" value="Unmap" id="btnUnmap" />
        </div>
        <div style="width: 235px; float:left;">
          <div>Data Mapped Columns</div>
          <select id="dataMappedCols" size="10" style="width:200px">
          </select>
        </div>
      </div>
    </fieldset>
    <div style="width:600px">
      <input type="button" id="btnImport" value="Import Data" />
    </div>
  </div>
EOT;

/*
  $html .= createDropdownOptions($win_observations_cols);
  $html .= createDropdownOptions($win_observation_location_cols);
  $html .= createDropdownOptions($win_observer_cols);
*/

  return $js.$html;
}

function createDropdownOptions($opts) {
  $options = array();

  foreach($opts as $value => $text) {
    $options[] = '<option value="'.$value.'">'.$text.'</option>';
  }

  return implode('', $options);
}

function upload_bulk_data($params) {
  global $user;
  if (!win_observation_user_has_edit_permissions()) {
    die(json_encode(set_error_arr("You are not authorized.")));
  }

  $fileElem = $params['fileElem'];
  $delimiter = $params['delimiter'];
  $enclosure = $params['enclosure'];
  if (empty($enclosure)) {
    $enclosure = '"';
  }

  $_FILESinfo = $_FILES[$fileElem];

  if ($_FILESinfo['error'] > 0) {
    die(json_encode(set_error_arr('File upload error')));
  }

  $fname = $_FILESinfo['name'];

  $strrpos = strrpos($fname, '.');
  if ($strrpos === FALSE) {
    die(json_encode(set_error_arr('Incorrect file type. Only .csv are allowed')));
  }
  $ext = substr($fname, $strrpos);
  if ($ext != '.csv') {
    die(json_encode(set_error_arr('Incorrect file type. Only .csv are allowed')));
  }

  // get the local path on the machine
  $uploadpath = dirname(realpath('index.php')) . '/sites/default/files/bulk/';

  if (!is_dir($uploadpath)) {
    mkdir($uploadpath, 0777, TRUE);
  }

  $uniqid = uniqid();
  $filename = $uniqid . '_' . $fname;
  $file_pth = $uploadpath . $filename;
  move_uploaded_file($_FILESinfo["tmp_name"], $file_pth);

  if (($handle = fopen($file_pth, "r")) !== FALSE) {
    if (($data_cols = fgetcsv($handle, 0, $delimiter, $enclosure)) !== FALSE) {
      $response = set_no_error_arr("");
      $response['filename'] = $filename;
      $response['data_cols'] = $data_cols;
      die(json_encode($response));
    }
  }
  die(json_encode(set_error_arr('Error reading file.')));
}

function import_bulk_data($params) {
  global $user;
  if (!win_observation_user_has_edit_permissions()) {
    die(json_encode(set_error_arr("You are not authorized.")));
  }

  $filename = $params['filename'];
  $delimiter = $params['delimiter'];
  $enclosure = $params['enclosure'];
  $mappings = $params['mapping'];

  $uploadpath = dirname(realpath('index.php')) . '/sites/default/files/bulk/';
  $filepath = $uploadpath . $filename;

  $tables_cols_mapping = array();
  $tables_cols_mapping['win_observations'] = array();
  $tables_cols_mapping['win_observation_location'] = array();
  $tables_cols_mapping['win_observer'] = array();

  $data_mapping = array();
  $lat = -1;
  $lon = -1;
  foreach ($mappings as $mapping) {
    if ($mapping['dbcol'] == 'latitude') {
      $lat = $mapping['datacol'];
    }
    elseif ($mapping['dbcol'] == 'longitude') {
      $lon = $mapping['datacol'];
    }
    else {
      /*
      list($db_table, $db_col) = explode('.', $mapping['dbcol']);
      $tables_cols_mapping[$db_table][$db_col] = $mapping['datacol'];
      */
      $data_mapping[$mapping['datacol']] = $mapping['dbcol'];
    }
  }

  if ($lat == -1 || $lon == -1) {
    die(json_encode(set_error_arr("Latitude and Longitude columns not specified.")));
  }

  $i = 0;
  $row_cnt = 0;
  if (($handle = fopen($filepath, "r")) !== FALSE) {
    if (($data_cols = fgetcsv($handle, 0, $delimiter, $enclosure)) !== FALSE) { // Skip first row
    }
    while (($data_cols = fgetcsv($handle, 0, $delimiter, $enclosure)) !== FALSE) {
      $row_cnt++;
      //$tables_cols_mapping[$db_table][$i] = array();
      $arr = array();
      $mapped_keys = array_keys($data_mapping);
      $latval = $data_cols[$lat];
      $lonval = $data_cols[$lon];
      $waterbody_type = '';
      $waterbody_name = '';
      if (!empty($latval) && !empty($lonval)) {
        foreach ($data_cols as $indx => $val) {
          if (array_key_exists($indx, $data_mapping)) {
            if ($data_mapping[$indx] == 'win_observations.waterbody_type') {
              $waterbody_type = $val;
            }
            if ($data_mapping[$indx] == 'win_observations.waterbody_name') {
              $waterbody_name = $val;
            }
            list($db_table, $db_col) = explode('.', $data_mapping[$indx]);
            $tables_cols_mapping[$db_table][$i][$db_col] = $val;
          }
        }
        $tables_cols_mapping['win_observations'][$i]['topology'] = 'POINT(' . $latval . ' ' . $lonval . ')';
        if (!empty($waterbody_type) || !empty($waterbody_name)) {
          $tables_cols_mapping['win_observations'][$i]['is_waterbody_related'] = '1';
        }
        $i++;
      }
    }
  }

  $len = $i;
  if ($len == 0) {
    if ($row_cnt == 0) {
      die(json_encode(set_error_arr("Error reading data from file.")));
    }
    else {
      die(json_encode(set_error_arr("All rows have been ignored as they do not contain latitude longitude values.")));
    }
  }

  for ($i = 0; $i < $len; $i++) {
    $observer_id = add_observer_details($tables_cols_mapping['win_observer'][$i]);
    $tables_cols_mapping['win_observations'][$i]['observer_id'] = $observer_id;
    $observation_id = insert_observation_record($tables_cols_mapping['win_observations'][$i], array(), $tables_cols_mapping['win_observation_location'][$i], array());
  }

  if ($handle !== FALSE) {
    fclose($handle);
    unlink($filepath);
  }
  die(json_encode(set_no_error_arr("Data imported successfully. Will be verfied for authenticity.")));
}

function add_observer_details($observer_details) {
  if (count($observer_details) == 0) {
    $id = get_current_user_observerid();
  }
  else {
    $query = "select id from win_observer where ";
    $where = array();
    foreach ($observer_details as $key => $val) {
      $where[] = "$key = '$val'";
    }
    $query .= implode(' and ', $where);
    //$query = "select id, fname || ' ' || lname as name from win_observer where email = '{$email}'";
    $result = db_query($query);
    if (!$result) {
    }
    else {
      if ($obj = db_fetch_object($result)) {
        $id = $obj->id;
      }
    }

    if ($id == 0) {
      $keys = array();
      $vals = array();
      foreach ($observer_details as $key => $val) {
        $keys[] = $key;
        $vals[] = "'" . $val . "'";
      }
      $query = 'insert into win_observer ('.implode(',', $keys).') values ';
      //$query .= "('$fname', '$lname', '$affiliation', '$address', '$mobile', '$phoneno', '$email')";
      $query .= '('.implode(',', $vals).')';
      $result = db_query($query);
      if (!$result) {
        return false;
      }
      else {
        $query = "select last_value from win_observer_id_seq";
        $result = db_query($query);
        if (!$result) {
          return false;
        }
        else {
          if ($arr = db_fetch_object($result)) {
            return $arr->last_value;
          }
          else {
            return false;
          }
        }
      }
    }
  }
  return $id;
}